# -*- coding:utf-8 -*-
import json
import requests
import csv
import pymysql
'''
功能：食物功效接口
思路：
1、从csv中读取文件，作为食物功效接口的参数
2、保存接口响应结果的数据到csv文件/或者到本地数据库
'''
class FoodEffect():


    def login_token(self):
        # 初始化登录接口
        login_url = 'https://api.icarbonx.com/oauth2/token?grant_type=password&sms_verify=true'
        login_header = {"Authorization": "Basic Y29tLm1ldW0uY29hY2guaXBob25lOjdmYTYyZmFmYzgxZTgwMzY="}
        login_data = {
            "username": "15012345678",
            "password": "888888",
            "appName": "health-buddy",
            "grant_type": "password",
            "sms_verify": "true"
        }
        # 登录请求接口
        r_login = requests.post(url=login_url, data=login_data, headers=login_header)
        # 获取登录的响应报文
        print(r_login.text)
        login_response = json.loads(r_login.text)
        # 保存登录的token信息
        access_token = login_response['access_token']
        return access_token

    def TestEffect(self):

        # 连接本地数据库
        conn = pymysql.connect(host='localhost',
                               port=3306,
                               user='root',
                               password='111111',
                               db='test', charset='utf8')

        # 通过获取到的数据库连接conn下的cursor()方法来创建游标
        cur = conn.cursor()

        # 创建数据库
        '''ENGINE=INNODB 表示将数据库的引擎设置为InnoDB,从MySQL 5.6开始默认使用该引擎。

        AUTO_INCREMENT=10 表示自动增长的起始值为10

        DEFAULT CHARSET=utf8表示设置数据库的默认字符集为utf8
        '''
        cur.execute("DROP TABLE IF EXISTS food_effect")
        conn.commit()
        create_db = '''create table food_effect(name_id INT NOT NULL AUTO_INCREMENT,name varchar(500),PRIMARY KEY(name_id),name_msg varchar(100)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin'''
        cur.execute(create_db)  # 执行创建表语句
        conn.commit()

        # 初始化
        food_flag_init = 0
        food_error_init = 0
        food_fail_init = 0


        # 读取csv文件
        with open('E:\\test\\HB\\food_effect_init1.csv', 'r') as csvFile:
            reader = csv.reader(csvFile)
            print(type(reader))
            next(reader)
            for row in reader:
                print(row)
                print(type(row))
                print(row[0])
                print(type(row[0]))

                # 食物qa问答接口的请求参数msg
                food_msg = {"msg": row[0]}
                print(food_msg)


                food_headers = {"Authorization": "Bearer " + self.login_token()}
                # 食物量词的url
                food_url = 'https://api.icarbonx.com/nlp/api/v1.0/nutri_food_qa'
                # qa问答模型接口请求
                r_food = requests.post(url=food_url, data=food_msg, headers=food_headers)
                # 获取响应报文
                print(r_food.text)
                # 转换响应结果为dict格式
                food_response = json.loads(r_food.text)
                print(food_response)
                print(type(food_response))

                # 判断响应结果是否为空,不为空，则获取food和name的名称,此处的响应结果是json
                if food_response:
                    print(food_response)
                    food_response_all = food_response['text']
                    print(type(food_response_all))

                    print(food_response_all)
                    food_effect_data = ['effect_msg','food_effect']
                    food_effect_data[0] = row[0]
                    food_effect_data[1] = food_response_all
                    # food_effect_data.append(food_response_all)
                    food_effect_data = zip(food_effect_data)
                    print(type(food_effect_data))
                    print(food_effect_data)
                    print(len(food_effect_data))

                    # for k in food_effect_data:
                    #     print("ok")

                    # 执行insert
                    insert_db = "insert into food_effect(name_msg,name) values(%s,%s)"
                    cur.executemany(insert_db, food_effect_data)  # 执行高效插入
                    conn.commit()

            # 关闭连接
            if cur:
                cur.close()
            if conn:
                conn.close()


        csvFile.close()

if __name__ == '__main__':
    fd = FoodEffect()
    fd.TestEffect()
